﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using Follow.Utils;
using Follow.Model;
using System.Transactions;

namespace Follow.DataAccess
{
    public class MetaFuncionarioDAO
    {
        private SqlDataReader reader = null;
        private SqlConnection conn = null;
        private SqlCommand cmd = null;

        public MetaFuncionarioDAO()
        {
            conn = ConnectionUtils.getConnection();
        }
       

        public IList<MetaFuncionario> findByFuncionario(Int32 id)
        {
            IList<MetaFuncionario> mf = new List<MetaFuncionario>();
            try
            {
                conn.Open();
                String sqlCommand = " select mf.*, m.nome from metafuncionario mf, meta m " +
                                    " where mf.funcionario_idfuncionario = @id and mf.meta_idmeta = m.idmeta;";
                cmd = new SqlCommand(sqlCommand, conn);
                cmd.Parameters.Add(new SqlParameter("id", id));
                reader = cmd.ExecuteReader();


                while (reader.Read())
                {
                    MetaFuncionario metfunc = new MetaFuncionario();
                    metfunc.IDMetaFuncionario = reader.GetInt32(0);
                    metfunc.IDFuncionario = reader.GetInt32(1);
                    metfunc.IDMeta = reader.GetInt32(2);
                    metfunc.Quantidade = reader.GetDecimal(3);
                    metfunc.DataInicial = reader.GetDateTime(4);
                    metfunc.DataFinal = reader.GetDateTime(5);
                    metfunc.NomeMeta = reader.GetString(6);

                    mf.Add(metfunc);
                }

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }
            }
            return mf;
        }


        public MetaFuncionario insert(MetaFuncionario mf, int idUsuario)
        {
            using (var transaction = new TransactionScope())
            {
                try
                {
                    conn.Open();
                    String sqlCommand = " insert into metafuncionario (funcionario_idfuncionario, meta_idmeta,quantidade, datainicial, datafinal)" +
                                    "values (@idfunc, @idmeta,@quantidade, @datainicial, @datafinal); " +
                                    "select * from metafuncionario where idmetafuncionario=@@IDENTITY;";
                    cmd = new SqlCommand(sqlCommand, conn);
                    cmd.Parameters.Add(new SqlParameter("idfunc", mf.IDFuncionario));
                    cmd.Parameters.Add(new SqlParameter("idmeta", mf.IDMeta));
                    cmd.Parameters.Add(new SqlParameter("quantidade", mf.Quantidade));
                    cmd.Parameters.Add(new SqlParameter("datainicial", mf.DataInicial));
                    cmd.Parameters.Add(new SqlParameter("datafinal", mf.DataFinal));
                    cmd.ExecuteNonQuery();
                    transaction.Complete();
                }
                catch (Exception)
                {
                    transaction.Dispose();
                    throw;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }

                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }
            LogDAO log = new LogDAO();
            log.insert(idUsuario, Common.SQLStringSingleton.GetCommandText(cmd), "Insert", "Associar Meta a Funcionario");
            return mf;
        }



        public void delete(int id, int idUsuario)
        {
            String sqlCommand;
            using (var transaction = new TransactionScope())
            {
                try
                {
                    conn.Open();
                    sqlCommand = " delete from metafuncionario where idmetafuncionario = "+ id;
                    cmd = new SqlCommand(sqlCommand, conn);
                    cmd.ExecuteNonQuery();
                    transaction.Complete();
                }
                catch (Exception)
                {
                    transaction.Dispose();
                    throw;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }

                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }
            LogDAO log = new LogDAO();
            log.insert(idUsuario, sqlCommand, "Delete", "Associar Metas a Funcionários");
        }

        public void update(Meta m, int idUsuario)
        {
            //a fazer
        }
      
    }
}